package com.pineapple.module.core.standards.area.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.pineapple.module.core.standards.area.domain.Area;
import com.pineapple.module.core.standards.area.domain.AreaCriteria;
import com.pineapple.module.core.standards.area.domain.AreaSearch;



public class AreaDAO {

	protected List<AreaSearch> search(AreaCriteria criteria,Connection conn) throws Exception{
		List<AreaSearch> lstarea = new ArrayList<AreaSearch>();
		AreaSearch area = null;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM area ");
		sql.append("WHERE 1=1 ");

		if(criteria.getAreaId()!=null && !criteria.getAreaId().equals("")){
			sql.append("AND area_id  LIKE '"+criteria.getAreaId()+"%' ");
		}
		if(criteria.getAreaName()!=null && !criteria.getAreaName().equals("")){
			sql.append("AND area_name LIKE '"+criteria.getAreaName()+"%' ");
		}
		if(criteria.getAreaSize() != null && !criteria.getAreaSize().equals("")){
			sql.append("AND area_size LIKE '"+criteria.getAreaSize()+"%' ");
		}
		/*if(criteria.getAreaStatus()!=null && !criteria.getAreaStatus().equals("")){
			sql.append("AND area_status LIKE '"+criteria.getAreaStatus()+"%' ");
		}*/


		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
		        while(rs.next()) {
		        	area= new AreaSearch();
		        	area.setAreaId(rs.getString("area_id"));
		        	area.setAreaName(rs.getString("area_name"));
		        	area.setAreaSize(rs.getString("area_size"));
		        //	area.setAreaStatus(rs.getString("area_status"));


		        	lstarea.add(area);
		        }


		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lstarea;
	}



	protected void add(Connection conn, Area area)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO area (area_name,area_size)");
		sql.append("VALUES (");

		sql.append("'"+area.getAreaName()+"' ");
		sql.append(",'"+area.getAreaSize()+"' ");
		//sql.append(",'"+area.getAreaStatus()+"' ");
		sql.append(" )");

			System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			 stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}
	protected void edit(Connection conn, Area area)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE area SET ");
		sql.append("area_name='"+area.getAreaName()+"' ");
		sql.append(",area_size='"+area.getAreaSize()+"' ");
		//sql.append(",area_status='"+area.getAreaStatus()+"' ");
		sql.append(" WHERE area_id="+area.getAreaId());


		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

	protected void delete(Connection conn, String areaIds)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM area  ");

		sql.append(" WHERE area_id IN ("+areaIds+")");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}


	protected Area searchById(Connection conn,int id)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM area ");
		sql.append("WHERE area_id ="+id);
		Area area = null;
		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
			   area = new Area();
		        if(rs.next()) {
		        	area.setAreaId(rs.getString("area_id"));
		        	area.setAreaName(rs.getString("area_name"));
		        	area.setAreaSize(rs.getString("area_size"));
		        	//area.setAreaStatus(rs.getString("area_status"));

		        }

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return area;
	}
}
